﻿//=====================================================================
//
// File: FilestreamLoader.main
//
// Summary: Loads filestream data into SQL Server
//
// Date: November 11, 2007
//
// Authors:
//   Roger Doherty - http://blogs.msdn.com/rdoherty/
//   Zach Skyles Owens - http://blogs.msdn.com/zowens/
//
//---------------------------------------------------------------------
//
// This file is part of the Microsoft SQL Server Community Samples.
// Copyright (C) Microsoft Corporation.  All rights reserved.
//
// This source code is intended only as a supplement to Microsoft
// Development Tools and/or on-line documentation.  See these other
// materials for detailed information regarding Microsoft code samples.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
//
//===================================================================== 
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.Win32.SafeHandles;
using SqlFilestream;

namespace FilestreamLoader
{
    class Program
    {
        // Sets the buffer size for reading and writing blobs.  
        // Used NTFS blocksize which is 512K.
        const int blockSize = 1024*512; 
        
        static void Main()
        {
            // Setup file parameters.
            // Change the fileName in the file and in WPF XAML to load different media
            String contentType = "video/x-ms-wmv";
            String filePath = @"..\..\..\..\..\Media\";
            String fileName = @"RogAndZachOnFilestream.wmv";

            #region Database connection
            // Open a connection to the database.
            SqlConnectionStringBuilder cxnString = new SqlConnectionStringBuilder();
            cxnString["Data Source"] = "(local)";
            cxnString["Integrated Security"] = true;
            cxnString["Initial Catalog"] = "FileStreamWpfHttp";
            SqlConnection cxn = new SqlConnection(cxnString.ConnectionString);
            cxn.Open();
            #endregion Database connection

            // Start up a database transaction.
            SqlTransaction txn = cxn.BeginTransaction();

            // Create a Guid to be used on insert.
            Guid mediaId = Guid.NewGuid();

            // Insert a row into the table to create a handle for streaming write.
            SqlCommand cmd = new SqlCommand("INSERT [dbo].[media]([mediaId], [fileName], [contentType]) VALUES( @mediaId, @fileName, @contentType);", cxn, txn);
            cmd.Parameters.Add("@mediaId", SqlDbType.UniqueIdentifier).Value = mediaId;
            cmd.Parameters.Add("@fileName", SqlDbType.NVarChar, 256).Value = fileName;
            cmd.Parameters.Add("@contentType", SqlDbType.NVarChar, 256).Value = contentType;
            cmd.ExecuteNonQuery();

            // Get a filestream PathName token and filestream transaction context.
            // These items will be used to open up a file handle against the empty blob instance.
            cmd = new SqlCommand("SELECT [file].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM [dbo].[media] WHERE [mediaId] = @mediaId;", cxn, txn);
            cmd.Parameters.Add("@mediaId", SqlDbType.UniqueIdentifier).Value = mediaId;

            // Read in results of query.
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
            rdr.Read();
            SqlString sqlFilePath = rdr.GetSqlString(0);
            SqlBinary transactionToken = rdr.GetSqlBinary(1);
            rdr.Close();

            // Get a Win32 file handle to the empty blob instance using SQL Native Client call.
            // This is required in order to write to the empty blob instance.
            SafeFileHandle handle = SqlNativeClient.OpenSqlFilestream(
                    sqlFilePath.Value, 
                    SqlNativeClient.DESIRED_ACCESS_WRITE, 
                    0, 
                    transactionToken.Value, 
                    (UInt32)transactionToken.Value.Length, 
                    new SqlNativeClient.LARGE_INTEGER_SQL(0));

            // Open up a new stream to write the file to the blob.
            FileStream destBlob = new FileStream(handle, FileAccess.Write);

            // Open up a new stream to read the source media file.
            FileStream sourceFile = new FileStream(String.Concat(filePath, fileName), FileMode.Open, FileAccess.Read);

            // Loop through source file and write to FileStream handle
            // ATTENTION: This is the power of FileStream!!!
            byte[] buffer = new byte[blockSize];
            int bytesRead;
            while ((bytesRead = sourceFile.Read(buffer, 0, buffer.Length)) > 0)
            {
                destBlob.Write(buffer, 0, bytesRead);
            }

            #region Connection cleanup
            // Commit transaction, cleanup connection.
            destBlob.Close();
            sourceFile.Close();            
            txn.Commit();
            cxn.Close();
            #endregion Connection cleanup
        }
    }
}
